﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OracleClient;
using DBUtility;

namespace OracleDAL.DutyAssignment
{
    public class D_DutyPerson
    {
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select USERISN,DUTYID,SHOWORDER ");
            strSql.Append(" FROM DUTY_ARRANGE_PERSON ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }

            strSql.Append(" order by showorder");
            return OracleHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(string USERISN)
        {
            string strSql = "select count(1) FROM DUTY_ARRANGE_PERSON where userisn=:USERISN";
            OracleParameter[] parameters = {
					new OracleParameter(":USERISN", OracleType.VarChar,50)};
            parameters[0].Value = USERISN;

            return  OracleHelper.Exists(strSql, parameters);
        }

        /// <summary>
        ///  增加一条数据
        /// </summary>
        public bool Add(Model.DutyAssignment.M_DutyPerson model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO DUTY_ARRANGE_PERSON(USERISN,DUTYID,SHOWORDER) ");
            strSql.Append("VALUES(:USERISN,:DUTYID,:SHOWORDER)");
            OracleParameter[] parameters = {
					new OracleParameter(":USERISN", OracleType.VarChar,50),
					new OracleParameter(":DUTYID", OracleType.VarChar,50),
					new OracleParameter(":SHOWORDER", OracleType.VarChar,20)};
            parameters[0].Value = model.USERISN;
            parameters[1].Value = model.DUTYID;
            parameters[2].Value = model.SHOWORDER;

            return OracleHelper.ExecuteSql(strSql.ToString(), parameters) > 0 ? true : false;
        }

        /// <summary>
        ///  更新一条数据
        /// </summary>
        public bool Update(Model.DutyAssignment.M_DutyPerson model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE DUTY_ARRANGE_PERSON SET USERISN=:USERISN,DUTYID=:DUTYID,SHOWORDER=:SHOWORDER");
            strSql.Append(" WHERE USERISN=:USERISN ");
            OracleParameter[] parameters = {
					new OracleParameter(":USERISN", OracleType.VarChar,50),
					new OracleParameter(":DUTYID", OracleType.VarChar,50),
					new OracleParameter(":SHOWORDER", OracleType.VarChar,20)};
            parameters[0].Value = model.USERISN;
            parameters[1].Value = model.DUTYID;
            parameters[2].Value = model.SHOWORDER;

            return OracleHelper.ExecuteSql(strSql.ToString(), parameters) > 0 ? true : false;
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(string USERISN)
        {
            string strSql = "Delete from DUTY_ARRANGE_PERSON where userisn=:USERISN";
            OracleParameter[] parameters = {
					new OracleParameter(":USERISN", OracleType.VarChar,50)};
            parameters[0].Value = USERISN;

            return OracleHelper.ExecuteSql(strSql, parameters) > 0 ? true : false;
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Model.DutyAssignment.M_DutyPerson GetModel(string USERISN)
        {
            string strSql = "SELECT * FROM DUTY_ARRANGE_PERSON WHERE USERISN=:USERISN ";
            OracleParameter[] parameters = {
					new OracleParameter(":USERISN", OracleType.VarChar,50)};
            parameters[0].Value = USERISN;

            Model.DutyAssignment.M_DutyPerson model = new Model.DutyAssignment.M_DutyPerson();
            DataSet ds = OracleHelper.Query(strSql, parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                model.USERISN = ds.Tables[0].Rows[0]["USERISN"].ToString();
                model.DUTYID = ds.Tables[0].Rows[0]["DUTYID"].ToString();
                model.SHOWORDER = ds.Tables[0].Rows[0]["SHOWORDER"].ToString();
                return model;
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 用于值班人员设置时用，一次性插入多个值班人员
        /// </summary>
        /// <param name="userIsns"></param>
        /// <param name="dutyID"></param>
        /// <param name="unitisn"></param>
        /// <param name="zdr"></param>
        /// <returns></returns>
        public bool AddMultiModel(string userIsns,string dutyID,string unitisn,string zdr)
        {
            List<string> strSql = new List<string>();
            string[] UserIsnCollection = userIsns.Split(',');
            if (!new D_Duty().Exists(dutyID))
            {
                dutyID = RandomIDss();
                strSql.Add("insert into DUTY_ARRANGE_new(id,unitisn,zdr) values('" + dutyID + "','" + unitisn + "','"+ zdr +"')");
            }
            for (int i = 0; i < UserIsnCollection.Length; i++)
            {
                if (Exists(UserIsnCollection[i]))
                {
                    strSql.Add("update DUTY_ARRANGE_PERSON set dutyid='" + dutyID + "',showorder='" + i + "' where userisn='" + UserIsnCollection[i] + "'");
                }
                else
                {
                    strSql.Add("insert into DUTY_ARRANGE_PERSON(userisn,dutyid,showorder) values('"+ UserIsnCollection[i] +"','"+ dutyID +"','"+ i +"')");
                }
            }

            return OracleHelper.ExecuteSqlTran(strSql) > 0 ? true : false;
        }


        /// <summary>
        /// 创建时间格式(yyyyMMddHHmmss) + 随机数字三位随机数表示
        /// </summary>
        /// <returns></returns>
        public static string RandomIDss()
        {
            string strTime = DateTime.Now.ToString("yyyyMMddHHmmss");

            Random ran = new Random(GetRandomSeed());
            strTime += ran.Next(0, 999).ToString();

            return strTime;

        }

        private static int GetRandomSeed()
        {
            byte[] bytes = new byte[4];
            System.Security.Cryptography.RNGCryptoServiceProvider rng = new System.Security.Cryptography.RNGCryptoServiceProvider();
            rng.GetBytes(bytes);

            return BitConverter.ToInt32(bytes, 0);
        }
    }
}
